from collections import defaultdict, Counter
from datetime import datetime, timedelta

import pandas as pd
from django.db.models import Count, F, Avg, Q
from django.http import JsonResponse
from risk.models import *
from risk.utils import rest_response


def pie_group_counter(x_set, group_dict):
    res = []
    for k, v in group_dict.items():
        counter = Counter(v)
        count_nv = [
            {"name": x_item, "value": counter.get(x_item, 0)} for x_item in x_set
        ]
        res.append({"data": count_nv})
    return res


@rest_response
def get_total_enterprises(request, *args, **kwargs):
    # 查询企业总数
    return {
        "count": Pingfen.objects.values("企业名称").distinct().count()
    }


@rest_response
def get_industry_counts(request, *args, **kwargs):
    return {
        # 查询各行业企业数量
        "industry_counts": list(Pingfen.objects.values('行业').annotate(count=Count('企业名称', distinct=True)))
    }


def get_area_counts(request, *args, **kwargs):
    response = {"data": dict(
        # 查询各区域企业数量
        area_counts=list(Pingfen.objects.values('辖区').annotate(count=Count('企业名称', distinct=True)))
    )}

    response |= {
        "code": 2000,
        "msg": "success"
    }

    return JsonResponse(response)


"""# 查询各行业企业数量
industry_counts = Pingfen.objects.values('行业').annotate(count=Count('企业名称', distinct=True)).distinct()
"""


@rest_response
def get_area_industry_count(request, *args, **kwargs):
    return dict(
        # 查询各区域企业数量
        # TODO 14
        area_counts_1=list(
            Pingfen.objects.filter(行业="服装").values('辖区').annotate(count=Count('企业名称', distinct=True))),
        area_counts_2=list(
            Pingfen.objects.filter(行业="眼镜").values('辖区').annotate(count=Count('企业名称', distinct=True)))
    )


"""
# 查询各等级企业数量
grade_counts = Pingfen.objects.filter("抽检年度").values('评分等级').annotate(count=Count('企业名称', distinct=True))

# 查询各年度企业平均分
avg_scores_by_year = Pingfen.objects.values('抽检年度').annotate(avg_score=Avg(F('产品风险因素') + F('企业通用信用风险因素') + F('企业质量信用风险因素')))

avg_scores_by_year_and_area = (
    Pingfen.objects.values('抽检年度', '辖区')
    .annotate(avg_score=Avg(F('产品风险因素') + F('企业通用信用风险因素') + F('企业质量信用风险因素')))
)"""

# 生成所有月份
ALL_MONTH = ['2018-01', '2018-02', '2018-03', '2018-04', '2018-05', '2018-06', '2018-07', '2018-08', '2018-09',
             '2018-10', '2018-11', '2018-12', '2019-01', '2019-02', '2019-03', '2019-04', '2019-05', '2019-06',
             '2019-07', '2019-08', '2019-09', '2019-10', '2019-11', '2019-12', '2020-01', '2020-02', '2020-03',
             '2020-04', '2020-05', '2020-06', '2020-07', '2020-08', '2020-09', '2020-10', '2020-11', '2020-12',
             '2021-01', '2021-02', '2021-03', '2021-04', '2021-05', '2021-06', '2021-07', '2021-08', '2021-09',
             '2021-10', '2021-11', '2021-12', '2022-01', '2022-02', '2022-03', '2022-04', '2022-05', '2022-06',
             '2022-07', '2022-08', '2022-09', '2022-10', '2022-11', '2022-12', '2023-01', '2023-02', '2023-03',
             '2023-04', '2023-05', '2023-06', '2023-07', '2023-08', '2023-09', '2023-10', '2023-11', '2023-12']

ALL_YEAR = ["2018", "2019", "2020", "2021", "2022", "2023"]


# 投诉举报

@rest_response
def get_type_data(request, *args, **kwargs):
    tmp = list(Tousujvbao.objects.values("登记时间", "类型"))
    ts = defaultdict(int)
    jb = defaultdict(int)
    total = defaultdict(int)
    for i in tmp:
        t = i["登记时间"].strftime("%Y-%m")
        total[t] += 1
        if i["类型"] == "投诉":
            ts[t] += 1
        if i["类型"] == "举报":
            jb[t] += 1

    return {
        "month": ALL_MONTH,
        "ts": [ts[m] for m in ALL_MONTH],
        "jb": [jb[m] for m in ALL_MONTH],
        "total": [total[m] for m in ALL_MONTH]
    }


def get_status_data(request, *args, **kwargs):
    response = {"data": {}}

    tmp = list(Tousujvbao.objects.values("登记时间", "状态"))
    reject, ing, done = defaultdict(int), defaultdict(int), defaultdict(int)
    for i in tmp:
        t = i["登记时间"].strftime("%Y")
        if i["状态"] == "不受理":
            reject[t] += 1
        if i["状态"] == "办理中":
            ing[t] += 1
        if i["状态"] == "已办结":
            done[t] += 1

    response["data"]["year"] = ALL_YEAR
    response["data"]["series"] = [{"data":
        [
            {"name": "不受理", "value": reject[m]},
            {"name": "办理中", "value": ing[m]},
            {"name": "已办结", "value": done[m]},
            {"value": reject[m] + ing[m] + done[m]}
        ]} for m in ALL_YEAR]

    response |= {
        "code": 2000,
        "msg": "success"
    }

    return JsonResponse(response)


def get_area_data(request, *args, **kwargs):
    response = {"data": {}}

    tmp = list(Tousujvbao.objects.values("登记时间", "辖区"))
    areas = {i["辖区"] for i in tmp}
    year_data = {i: [] for i in ALL_YEAR}
    for i in tmp:
        t = i["登记时间"].strftime("%Y")
        year_data[t].append(i["辖区"])

    response["data"]["series"] = []

    for k, v in year_data.items():
        tmp1 = Counter(v)
        data = []
        for k1 in areas:
            value = tmp1.get(k1, 0)
            data.append({"name": k1, "value": value})
        response["data"]["series"].append({"data": data})

    response["data"]["year"] = ALL_YEAR

    response |= {
        "code": 2000,
        "msg": "success"
    }

    return JsonResponse(response)


# 行政处罚
@rest_response
def get_type_data_anjian_bar(request, *args, **kwargs):
    """
    条形图、折线图示例
    """
    tmp = list(Anjian.objects.values("立案日期", "案件类别"))
    types = {i["案件类别"] for i in tmp}
    year_data = {i: [] for i in ALL_YEAR}
    type_data = {j: [] for j in types}
    for i in tmp:
        t = i["立案日期"].strftime("%Y")
        year_data[t].append(i["案件类别"])

    for k, v in year_data.items():
        tmp1 = Counter(v)
        for k1, v1 in type_data.items():
            v1.append(tmp1.get(k1, 0))

    return {
        "year": ALL_YEAR,
        "series": type_data,
        "types": list(types)
    }


def get_type_data_anjian(request, *args, **kwargs):
    response = {"data": {}}

    tmp = list(Anjian.objects.values("立案日期", "案件类别"))
    types = {i["案件类别"] for i in tmp}
    year_data = {i: [] for i in ALL_YEAR}
    type_data = {j: [] for j in types}
    for i in tmp:
        t = i["立案日期"].strftime("%Y")
        year_data[t].append(i["案件类别"])

    response["data"]["series"] = []

    for k, v in year_data.items():
        tmp1 = Counter(v)
        data = []
        for k1 in types:
            value = tmp1.get(k1, 0)
            data.append({"name": k1, "value": value})
        response["data"]["series"].append({"data": data})

    """ for k, v in year_data.items():
        tmp1 = Counter(v)
        data = []
        for k1, v1 in type_data.items():
            v1.append(tmp1.get(k1,0))

    response["data"]["series"] = type_data"""

    response["data"]["year"] = ALL_YEAR

    response |= {
        "code": 2000,
        "msg": "success"
    }

    return JsonResponse(response)


def get_area_data_anjian(request, *args, **kwargs):
    response = {"data": {}}

    tmp = list(Anjian.objects.values("立案日期", "主要发案区域"))
    areas = {i["主要发案区域"][3:] for i in tmp}
    year_data = {i: [] for i in ALL_YEAR}
    for i in tmp:
        t = i["立案日期"].strftime("%Y")
        year_data[t].append(i["主要发案区域"][3:])

    response["data"]["series"] = []

    for k, v in year_data.items():
        tmp1 = Counter(v)
        data = []
        for k1 in areas:
            value = tmp1.get(k1, 0)
            data.append({"name": k1, "value": value})
        response["data"]["series"].append({"data": data})

    response["data"]["year"] = ALL_YEAR

    response |= {
        "code": 2000,
        "msg": "success"
    }

    return JsonResponse(response)


# 监督抽查

## 合格率
@rest_response
def get_type_data_jiandu_bar(request, *args, **kwargs):
    tmp = list(Jianduchoucha.objects.filter(结论="不合格").exclude(表名="市级监督复查").values("月份", "产品类别"))
    tmp1 = list(Jianduchoucha.objects.exclude(表名="市级监督复查")
                .values("月份", "产品类别").annotate(count=Count('id', distinct=True)))

    types = {i["产品类别"] for i in tmp1}
    # 各年度各类不合格项数
    year_data = {i: [] for i in ALL_YEAR[1:]}
    for i in tmp:
        t = i["月份"][:4]
        year_data[t].append(i["产品类别"])

    # 各年度各类总项数
    year_total_data = {i: {j: 0 for j in types} for i in ALL_YEAR[1:]}
    for i in tmp1:
        t = i["月份"][:4]
        year_total_data[t][i["产品类别"]] += i["count"]

    type_data = {j: [] for j in types}
    for k, v in year_data.items():
        tmp1 = Counter(v)
        for k1, v1 in type_data.items():
            d = year_total_data[k][k1]
            if d:
                v1.append(1 - tmp1.get(k1, 0) / d)
            else:
                v1.append(1 - tmp1.get(k1, 0))

    return {
        "year": ALL_YEAR[1:],
        "series": type_data,
        "types": list(types)
    }


## 不合格率与抽查批次
def get_data_jiandu_2(request, *args, **kwargs):
    response = {"data": {}}

    tmp = list(Jianduchoucha.objects.filter(结论="不合格").exclude(表名="市级监督复查").values("月份", "产品类别"))
    tmp1 = list(Jianduchoucha.objects.exclude(表名="市级监督复查")
                .values("月份", "产品类别").annotate(count=Count('id', distinct=True)))
    tmp2 = list(Jianduchoucha.objects.exclude(表名="市级监督复查")
                .values("月份").annotate(count=Count('id', distinct=True)))

    types = {i["产品类别"] for i in tmp1}
    # 各年度各类不合格项数
    year_data = {i: [] for i in ALL_YEAR[1:]}
    for i in tmp:
        t = i["月份"][:4]
        year_data[t].append(i["产品类别"])

    # 各年度各类总项数
    year_total_data = {i: {j: 0 for j in types} for i in ALL_YEAR[1:]}
    for i in tmp1:
        t = i["月份"][:4]
        year_total_data[t][i["产品类别"]] += i["count"]

    # 各年度总项数
    year_counts = {i: 0 for i in ALL_YEAR[1:]}
    for i in tmp2:
        t = i["月份"][:4]
        year_counts[t] += i["count"]

    type_data = {j: [] for j in types}
    for k, v in year_data.items():
        tmp1 = Counter(v)
        for k1, v1 in type_data.items():
            d = year_total_data[k][k1]
            if d:
                v1.append(tmp1.get(k1, 0) / d)
            else:
                v1.append(tmp1.get(k1, 0))

    response["data"]["types"] = list(types)
    response["data"]["series"] = type_data
    response["data"]["total_counts"] = list(year_counts.values())
    response["data"]["year"] = ALL_YEAR[1:]

    response |= {
        "code": 2000,
        "msg": "success"
    }

    return JsonResponse(response)


## 各区数量
@rest_response
def get_area_data_jiandu(request, *args, **kwargs):
    tmp = list(Jianduchoucha.objects.filter(结论="不合格").exclude(表名="市级监督复查").values("月份", "所在区"))
    areas = {i["所在区"] for i in tmp}
    year_data = {i: [] for i in ALL_YEAR[1:]}
    for i in tmp:
        t = i["月份"][:4]
        year_data[t].append(i["所在区"])

    return {
        "year": ALL_YEAR[1:],
        "series": pie_group_counter(areas, year_data)
    }


@rest_response
def get_data_jiandu_5(request, *args, **kwargs):
    # 待计数的数据
    tmp = list(Jianduchoucha.objects.exclude(表名="市级监督复查").values("月份", "产品类别"))
    # 组别
    types = {i["产品类别"] for i in tmp}
    # 各组的容器
    year_data = {i: [] for i in ALL_YEAR[1:]}  # 从2019年开始
    # 分组
    for i in tmp:
        t = i["月份"][:4]
        year_data[t].append(i["产品类别"])

    return {
        "year": ALL_YEAR[1:],
        "series": pie_group_counter(types, year_data)  # 计数
    }


# 时变饼图示例  get_area_data_jiandu
# 饼图示例


@rest_response
def get_data_1(request, *args, **kwargs):
    """
    监督抽查——合格率统计
    """

    tmp = list(Jianduchoucha.objects.filter(结论="不合格").exclude(表名="市级监督复查").values("月份", "产品类别"))
    # 各年度各类不合格项数
    year_data = {i: [] for i in ALL_YEAR[1:]}
    for i in tmp:
        t = i["月份"][:4]
        year_data[t].append(i["产品类别"])

    # 各年度各类总项数
    tmp1 = list(Jianduchoucha.objects.exclude(表名="市级监督复查")
                .values("月份", "产品类别").annotate(count=Count('id', distinct=True)))

    types = {i["产品类别"] for i in tmp1}
    year_total_data = {i: {j: 0 for j in types} for i in ALL_YEAR[1:]}
    for i in tmp1:
        t = i["月份"][:4]
        year_total_data[t][i["产品类别"]] += i["count"]

    # 计算合格率
    type_data = {j: [] for j in types}
    for k, v in year_data.items():
        tmp1 = Counter(v)
        for k1, v1 in type_data.items():
            d = year_total_data[k][k1]
            if d:
                v1.append(1 - tmp1.get(k1, 0) / d)
            else:
                v1.append(1 - tmp1.get(k1, 0))

    return {
        "year": ALL_YEAR[1:],
        "series": type_data,
        "types": list(types)
    }


@rest_response
def get_data_2(request, *args, **kwargs):
    tmp = list(Pingfen.objects.values("抽检年度", "评分等级"))
    areas = {i["评分等级"] for i in tmp}
    year_data = {i: [] for i in ALL_YEAR[1:-1]}
    for i in tmp:
        t = str(i["抽检年度"])
        year_data[t].append(i["评分等级"])

    return {
        "year": ALL_YEAR[1:-1],
        "series": pie_group_counter(areas, year_data)
    }


@rest_response
def get_data_3(request, *args, **kwargs):
    tmp = list(Pingfen.objects.filter(行业__in=["服装", "眼镜"]).values('抽检年度', '行业').annotate(
        avg_score=Avg(F('产品风险因素') + F('企业通用信用风险因素') + F('企业质量信用风险因素'))).order_by("抽检年度"))

    year = {str(i["抽检年度"]) for i in tmp}
    types = {i["行业"] for i in tmp}
    type_data = {i: {j: 0 for j in year} for i in types}
    for i in tmp:
        t = str(i["抽检年度"])
        type_data[i["行业"]][t] = i["avg_score"]

    return {
        "year": list(year),
        "series": {k: list(v.values()) for k, v in type_data.items()},
        "types": list(types)
    }


@rest_response
def get_data_4(request, *args, **kwargs):
    tmp = list(Pingfen.objects.values('抽检年度', '辖区').annotate(
        avg_score=Avg(F('产品风险因素') + F('企业通用信用风险因素') + F('企业质量信用风险因素'))).order_by("抽检年度"))

    year = {str(i["抽检年度"]) for i in tmp}
    types = {i["辖区"] for i in tmp}
    type_data = {i: {j: 0 for j in year} for i in types}
    for i in tmp:
        t = str(i["抽检年度"])
        type_data[i["辖区"]][t] = i["avg_score"]

    return {
        "year": list(year),
        "series": {k: list(v.values()) for k, v in type_data.items()},
        "types": list(types)
    }


@rest_response
def get_data_5(request, *args, **kwargs):
    import datetime
    start = request.GET.get("start", '2022-01-01')
    end = request.GET.get("end", '2023-01-01')
    st = datetime.datetime.strptime(start, '%Y-%m-%d')
    ed = datetime.datetime.strptime(end, '%Y-%m-%d')
    print(start, end)
    data = list(Jianduchoucha.objects.filter(抽样日期__range=(st, ed)).values("产品种类", "结论", "不合格项目", "所在区"))
    df = pd.DataFrame(data)
    df["结论"] = df["结论"].apply(lambda x: x == "不合格")
    groupd = df.groupby("产品种类")
    df2 = groupd["结论"].mean()
    count = (df2 != 0).sum()
    df2 = df2.sort_values(ascending=False).head(min(count,10)).sort_values(ascending=True)

    groupd2 = df.groupby(["产品种类", "不合格项目"])
    res1 = pd.DataFrame()
    res1["rate"] = groupd2.size() / df.groupby("产品种类").size()
    res1["area"] = list(groupd2["所在区"].unique())
    res1 = res1.reset_index()
    res1 = res1[res1["产品种类"].apply(lambda x: x in df2.index)]
    res1["total_rate"] = res1["产品种类"].apply(lambda x: groupd["结论"].mean()[x])

    series = res1.pivot(index='产品种类', columns='不合格项目', values='rate')
    series = series.fillna(0)
    series["total_rate"] = df2
    series = series.sort_values(by="total_rate", ascending=True)

    areas = res1.pivot(index='产品种类', columns='不合格项目', values='area')
    areas = areas.fillna('')
    areas = areas.applymap(lambda x: ','.join(x) if not isinstance(x, int) else 0)
    areas["total_rate"] = df2
    areas = areas.sort_values(by="total_rate", ascending=True)

    return {
        "type": list(df2.index),
        "series": series.to_dict(orient="list"),
        "areas": areas.to_dict(orient="dict")
    }


@rest_response
def get_data_9(request, *args, **kwargs):
    """
    舆情分析折线图
    """
    tmp = list(Yuqing.objects.values("日期", "区域"))
    types = {i["区域"] for i in tmp}
    year_data = {i: [] for i in ALL_YEAR}
    type_data = {j: [] for j in types}
    for i in tmp:
        t = i["日期"].strftime("%Y")
        year_data[t].append(i["区域"])

    for k, v in year_data.items():
        tmp1 = Counter(v)
        for k1, v1 in type_data.items():
            v1.append(tmp1.get(k1, 0))

    return {
        "year": ALL_YEAR,
        "series": type_data,
        "types": list(types)
    }


@rest_response
def get_data_10(request, *args, **kwargs):
    """
    饼图
    """
    tmp = Yuqing.objects.values('产品大类').annotate(count=Count('id', distinct=True))

    res = [{"name": i["产品大类"], "value": i["count"]} for i in tmp]
    return {
        "counts": res
    }


@rest_response
def get_data_14(request, *args, **kwargs):
    """
    饼图
    """
    tmp = Entity.objects.values('辖区').annotate(count=Count('企业名称', distinct=True))
    res = [{"name": i["辖区"], "value": i["count"]} for i in tmp]
    return {
        "counts": res
    }


@rest_response
def get_data_15(request, *args, **kwargs):
    area = request.GET.get("area")
    if area is not None and area != '总体':
        # 待计数的数据
        tmp = list(Pingfen.objects.filter(辖区=area).values("抽检年度", "评分等级"))
    else:
        tmp = list(Pingfen.objects.values("抽检年度", "评分等级"))
    # 组别
    types = {i["评分等级"] for i in tmp}
    # 各组的容器
    year_data = {i: [] for i in ALL_YEAR[1:-1]}  # 从2019年开始
    # 分组
    for i in tmp:
        t = str(i["抽检年度"])
        year_data[t].append(i["评分等级"])

    return {
        "year": list(year_data.keys()),
        "series": pie_group_counter(types, year_data)  # 计数
    }


@rest_response
def get_data_16(request, *args, **kwargs):
    """
    饼图
    """
    area = request.GET.get("area")
    if area == '总体':
        tmp = Pingfen.objects.filter(抽检年度=2022).values('评分等级').annotate(count=Count('企业名称', distinct=True))
    else:
        tmp = Pingfen.objects.filter(抽检年度=2022, 辖区=area).values('评分等级').annotate(
            count=Count('企业名称', distinct=True))

    res = [{"name": i["评分等级"], "value": i["count"]} for i in tmp]
    return {
        "counts": res
    }


@rest_response
def get_data_17(request, *args, **kwargs):
    tmp = list(Pingfen.objects.values('抽检年度', '评分等级').annotate(count=Count('企业名称', distinct=True)))
    types = ['A', 'B', 'C', 'D']
    year = {int(i): [] for i in ALL_YEAR[1:-1]}

    type_data = {j: {i: 0 for i in year.keys()} for j in types}
    for i in tmp:
        if i["评分等级"] in types and i["抽检年度"] in year:
            type_data[i["评分等级"]][i["抽检年度"]] = i["count"]
    type_data = {j: [v1 for k, v1 in v.items()] for j, v in type_data.items()}

    return {
        "year": list(year.keys()),
        "series": type_data,
        "types": list(types)
    }
